﻿EXEC ('
ALTER PROCEDURE [dbo].[medianamik_GetPagedNodesByType]
	@TypeId UNIQUEIDENTIFIER,
	@PropertyId UNIQUEIDENTIFIER,
	@CultureName VARCHAR(10),
	@PageNumber INT,
	@PageSize INT,
	@Revision INT = NULL
AS
BEGIN

DECLARE @FirstRow INT
DECLARE @LastRow INT
SET @FirstRow = (@PageNumber - 1) * @PageSize + 1
SET @LastRow = @FirstRow + @PageSize - 1

-- Première requête: nombre d''éléments
select COUNT(*) AS [Count]
from dbo.medianamik_Culture as c 
full outer join dbo.medianamik_node as n on c.culturename is not null
left join dbo.medianamik_NodePropertyValue as npv on npv.nodeid = n.nodeid and npv.culturename = c.culturename
and endrevision is null
and npv.propertyid = @PropertyId
where c.ContentManagementActive = 1
and n.typeid = @TypeId
and c.culturename = @CultureName and n.isdeleted = 0

-- Deuxième requête: les éléments paginés
SELECT NodeId, RowNum FROM 
(
	select n.nodeid,
	ROW_NUMBER() OVER(ORDER BY isnull(npv.value, 
	(select top 1 npv2.value from  dbo.medianamik_NodePropertyValue as npv2 
	where npv2.nodeid = n.nodeid
	and npv2.propertyid = @PropertyId
	and npv2.value is not null))) AS RowNum
	from dbo.medianamik_Culture as c 
	full outer join dbo.medianamik_node as n on c.culturename is not null
	left join dbo.medianamik_NodePropertyValue as npv on npv.nodeid = n.nodeid and npv.culturename = c.culturename
	and endrevision is null
	and npv.propertyid = @PropertyId
	where c.ContentManagementActive = 1
	and n.typeid = @TypeId
	and c.culturename = @CultureName and n.isdeleted = 0
) AS NodeIds
WHERE RowNum BETWEEN @FirstRow AND @LastRow
END')
IF NOT EXISTS (SELECT NULL FROM sys.objects WHERE object_id = OBJECT_ID(N'medianamik_GetPagedNodesByTypeWithFilter') AND type in (N'P', N'PC'))
BEGIN
EXEC('
CREATE PROCEDURE [dbo].[medianamik_GetPagedNodesByTypeWithFilter]
	@TypeId UNIQUEIDENTIFIER,
	@PropertyId UNIQUEIDENTIFIER,
	@CultureName VARCHAR(10),
	@Filter VARCHAR(MAX),
	@PageNumber INT,
	@PageSize INT,
	@Revision INT = NULL
AS
BEGIN

DECLARE @FirstRow INT
DECLARE @LastRow INT
SET @FirstRow = (@PageNumber - 1) * @PageSize + 1
SET @LastRow = @FirstRow + @PageSize - 1

-- Première requête: nombre d''éléments
select COUNT(*) AS [Count]
from dbo.medianamik_Culture as c 
full outer join dbo.medianamik_node as n on c.culturename is not null
left join dbo.medianamik_NodePropertyValue as npv on npv.nodeid = n.nodeid and npv.culturename = c.culturename
and endrevision is null
and npv.propertyid = @PropertyId
where c.ContentManagementActive = 1
and n.typeid = @TypeId
and c.culturename = @CultureName and n.isdeleted = 0
and isnull(npv.value, 
	(select top 1 npv2.value from  dbo.medianamik_NodePropertyValue as npv2 
	where npv2.nodeid = n.nodeid
	and npv2.propertyid = @PropertyId
	and npv2.value is not null)) LIKE ''%'' + @Filter + ''%''

-- Deuxième requête: les éléments paginés
SELECT NodeId, RowNum FROM 
(
	select n.nodeid,
	ROW_NUMBER() OVER(ORDER BY isnull(npv.value, 
	(select top 1 npv2.value from  dbo.medianamik_NodePropertyValue as npv2 
	where npv2.nodeid = n.nodeid
	and npv2.propertyid = @PropertyId
	and npv2.value is not null))) AS RowNum
	from dbo.medianamik_Culture as c 
	full outer join dbo.medianamik_node as n on c.culturename is not null
	left join dbo.medianamik_NodePropertyValue as npv on npv.nodeid = n.nodeid and npv.culturename = c.culturename
	and endrevision is null
	and npv.propertyid = @PropertyId
	where c.ContentManagementActive = 1
	and n.typeid = @TypeId
	and c.culturename = @CultureName and n.isdeleted = 0
	and isnull(npv.value, 
		(select top 1 npv2.value from  dbo.medianamik_NodePropertyValue as npv2 
		where npv2.nodeid = n.nodeid
		and npv2.propertyid = @PropertyId
		and npv2.value is not null)) LIKE ''%'' + @Filter + ''%''
) AS NodeIds
WHERE RowNum BETWEEN @FirstRow AND @LastRow
END')
GRANT EXECUTE ON [dbo].[medianamik_GetPagedNodesByTypeWithFilter] TO [Medianamik_DataAccess]
END
